package cn.shuangze.assetsms.dao.impl;

import cn.shuangze.assetsms.dao.AssetsInfoDao;
import cn.shuangze.assetsms.dao.DBUtil;
import cn.shuangze.assetsms.entity.AssetsInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static cn.shuangze.assetsms.dao.DBUtil.exceuteUpdate;
import static cn.shuangze.assetsms.dao.DBUtil.getConnection;

public class AssetsInfoDaoJDBCImpl implements AssetsInfoDao {

    @Override
    public int add(AssetsInfo assetsInfo) throws Exception {
        String sql = "insert into assets(name,TypeID,Model,Price,BuyDate,Status,Other) values(?,?,?,?,?,?,?)";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Object[]{
                    assetsInfo.getName(),
                    assetsInfo.getTypeID(),
                    assetsInfo.getModel(),
                    assetsInfo.getPrice(),
                    assetsInfo.getBuyDate(),
                    assetsInfo.getStatus(),
                    assetsInfo.getOther()
            });
        }
    }

    @Override
    public int delete(int id) throws Exception {
        String sql = "delete from assets where AssetsID=?";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Integer[]{id});
        }
    }

    @Override
    public int modify(AssetsInfo assetsInfo) throws Exception {
        String sql = "update assets set name=?,TypeID=?,Model=?,Price=?,BuyDate=?,Other=? where AssetsID=?";
        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Object[]{

                    assetsInfo.getName(),
                    assetsInfo.getTypeID(),
                    assetsInfo.getModel(),
                    assetsInfo.getPrice(),
                    assetsInfo.getBuyDate(),
                    assetsInfo.getOther(),
                    assetsInfo.getAssetsID(),
            });
        }
    }

    @Override
    public int modifyStatus(int id, String status) throws Exception {
        String sql = "update assets set Status=? where AssetsID=?";

        try (Connection conn = getConnection();) {
            return exceuteUpdate(conn, sql, new Object[]{status, id});
        }
    }

    @Override
    public String[][] findAll() throws Exception {
        return findByCondition(new AssetsInfo());
    }

    @Override
    public String[][] findAvailable() throws Exception {
        return findByCondition(new AssetsInfo("可用"));
    }

    @Override
    public String[][] findUsing() throws Exception {
        return findByCondition(new AssetsInfo("领用"));
    }

    @Override
    public String[][] findUsable() throws Exception {
        return findByCondition(new AssetsInfo("报废"));
    }

    @Override
    public String[][] findByCondition(AssetsInfo assetsInfo) throws Exception {

        StringBuffer sql = new StringBuffer("select * from assets where 1=1");

        if (assetsInfo == null)
            return null;

        if (assetsInfo.getAssetsID() != 0) {
            sql.append(" and AssetsID =").append(assetsInfo.getAssetsID());
        }
        if (assetsInfo.getTypeID() != 0) {
            sql.append(" and TypeID =").append(assetsInfo.getTypeID());
        }
        if (assetsInfo.getName() != null && !assetsInfo.getName().equals("")) {
            sql.append(" and name regexp '" + assetsInfo.getName() + "'");
        }
        if (assetsInfo.getModel() != null && !assetsInfo.getModel().equals("")) {
            sql.append(" and Model regexp '" + assetsInfo.getModel() + "'");
        }
        if (assetsInfo.getStatus() != null && !assetsInfo.getStatus().equals("")) {
            sql.append(" and Status regexp '" + assetsInfo.getStatus() + "'");
        }

        return getSqlResult(sql.toString());

    }


    private String[][] getSqlResult(String sql) throws Exception {
        List<AssetsInfo> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(sql);) {
            while (rs.next()) {
                list.add(new AssetsInfo(rs.getInt("AssetsID"),
                        rs.getString("name"),
                        rs.getInt("TypeID"),
                        rs.getString("Model"),
                        rs.getString("Price"),
                        rs.getDate("BuyDate"),
                        rs.getString("Status"),
                        rs.getString("Other")
                ));
            }
        }
        String[][] sn = null;
        int size = list.size();
        if (!list.isEmpty()) {
            sn = new String[list.size()][8];
        }
        for (int i = 0; i < size; i++) {
            sn[i][0] = Integer.toString(list.get(i).getAssetsID());
            sn[i][1] = list.get(i).getName();
            sn[i][2] = Integer.toString(list.get(i).getTypeID());
            sn[i][3] = list.get(i).getModel();
            sn[i][4] = list.get(i).getPrice();
            sn[i][5] = String.valueOf(list.get(i).getBuyDate());
            sn[i][6] = list.get(i).getStatus();
            sn[i][7] = list.get(i).getOther();
        }
        return sn;
    }


}
